﻿// Copyright (c) Microsoft Corporation.  All rights reserved. 
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;

namespace WiE.Model
{
    /// <summary>
    /// Local storage implementation of the WiE model (operations are performed to and from local data store)
    /// </summary>
    /// <remarks>
    /// 7-31-08 O.S.M Added IsAvailable() method to check on the availability / accessibility of the model.
    /// 8-11-08 O.S.M Added throw WiEModelException to better handle data errors in the application
    /// 10-05-08 O.S.M Added GetDevices() and fixed bug in GetDeviceByMember() added GetMembers() 
    /// 11-05-08 O.S.M Implemented GetMembers()
    /// </remarks>
    class WiEModelLocal : IWiEModel
    {
        private static string SQLCE_DBNAME = "WiELocalDatabase.sdf";

        // SQL Compact connection
        private SqlCeConnection m_sqlceConnection;

        // Location related prepared statements to Read / Delete and Save Location to the SQL Compact database
        private SqlCeCommand m_sqlceCommandInsertLocation;
        private SqlCeCommand m_sqlceCommandDeleteLocation;
        private SqlCeCommand m_sqlceCommandRetrieveLocation;
        private SqlCeCommand m_sqlceCommandRetrieveLocationsBetween;
        private SqlCeCommand m_sqlceCommandRetrieveLocationBefore;

        // Member related prepared statements to Read / Delete and Save Location to the SQL Compact database
        private SqlCeCommand m_sqlceCommandRetrieveMember;
        private SqlCeCommand m_sqlceCommandRetrieveMembers;
        private SqlCeCommand m_sqlceCommandRetrieveMemberByPhoneNumber;
        private SqlCeCommand m_sqlceCommandRetrieveMemberByUserName;
        private SqlCeCommand m_sqlceCommandInsertMember;
        private SqlCeCommand m_sqlceCommandUpdateMember;

        // Device related prepared statements to Read / Delete and Save Device recorsd to the SQL Compact Database
        private SqlCeCommand m_sqlceCommandRetrieveDevice;
        private SqlCeCommand m_sqlceCommandRetrieveDevicesByMember;
        private SqlCeCommand m_sqlceCommandRetrieveDevices;
        private SqlCeCommand m_sqlceCommandInsertDevice;
        private SqlCeCommand m_sqlceCommandUpdateDevice;

        /// <summary>
        /// Helper method that takes a dataReader and attempts to convert the current record into a LocationDataObject, this helper
        /// method is used in conjunction with the GetLocationXXX() methods.
        /// </summary>
        /// <remarks>
        /// 7/09/2008 - Added guidMemberID field to Locations
        /// </remarks>
        /// <param name="p_dataReader"></param>
        /// <returns></returns>
        private WiELocationDataObject GetLocationDataObjectFromDataReader(SqlCeDataReader p_dataReader)
        {
            WiELocationDataObject locationDataObject = null;

            if (p_dataReader.Read())
            {
                // We were able to retrieve the location record
                locationDataObject = new WiELocationDataObject();

                // Retrieve non-nullable fields
                locationDataObject.LocationID = (System.Guid)p_dataReader["guidLocationID"];
                locationDataObject.DeviceID = (System.Guid)p_dataReader["guidDeviceID_FK"];
                locationDataObject.MemberID = (System.Guid)p_dataReader["guidMemberID_FK"];
                locationDataObject.NumSatellites = (int)p_dataReader["nNumSatellites"];
                locationDataObject.Latitude = (double)p_dataReader["flLatitude"];
                locationDataObject.Longitude = (double)p_dataReader["flLongitude"];

                // Retrieve optional fields that could be set to DBNull
                if (DBNull.Value != p_dataReader["flHeading"])
                    locationDataObject.Heading = (double)p_dataReader["flHeading"];
                if (DBNull.Value != p_dataReader["flSpeed"])
                    locationDataObject.Speed = (double)p_dataReader["flSpeed"];
                if (DBNull.Value != p_dataReader["dtTimeCollected"])
                    locationDataObject.DateCollected = (DateTime)p_dataReader["dtTimeCollected"];
                if (DBNull.Value != p_dataReader["flAltitudeWRTEllipsoid"])
                    locationDataObject.AltitudeWRTEllipsoid = (double)p_dataReader["flAltitudeWRTEllipsoid"];
                if (DBNull.Value != p_dataReader["flAltitudeWRTSeaLevel"])
                    locationDataObject.AltitudeWRTSeaLevel = (double)p_dataReader["flAltitudeWRTSeaLevel"];
            }

            return (locationDataObject);
        }

        /// <summary>
        /// Helper method that takes a dataReader and attempts to convert the current record into a MemberDataObject, this helper
        /// method is used in conjunction with the GetMemberXXX() methods.
        /// </summary>
        /// <param name="p_dataReader"></param>
        /// <returns>
        /// - <bold>null</bold> if no current record.
        /// - A populated MemberDataObject for the current datareader record.
        /// </returns>
        private WiEMemberDataObject GetMemberDataObjectFromDataReader(SqlCeDataReader p_dataReader)
        {
            WiEMemberDataObject memberDataObject = null;

            if (p_dataReader.Read())
            {
                // We were able to retrieve the location record
                memberDataObject = new WiEMemberDataObject();

                // Retrieve non-nullable fields
                memberDataObject.MemberID = (System.Guid) p_dataReader["guidMemberID"];
                memberDataObject.UserName = p_dataReader["strUserName"].ToString();
                memberDataObject.Password = p_dataReader["strUserPassword"].ToString();

                // Retrieve optional fields that could be set to DBNull
                if (DBNull.Value != p_dataReader["strFirstName"])
                    memberDataObject.FirstName = p_dataReader["strFirstName"].ToString();

                if (DBNull.Value != p_dataReader["strLastName"])
                    memberDataObject.LastName = p_dataReader["strLastName"].ToString();

                if (DBNull.Value != p_dataReader["strEmail"])
                    memberDataObject.Email = p_dataReader["strEmail"].ToString();

                if (DBNull.Value != p_dataReader["strPhoneNumber"])
                    memberDataObject.PhoneNumber = p_dataReader["strPhoneNumber"].ToString();
            }

            return (memberDataObject);
        }

        /// <summary>
        /// Helper method that takes a datareader and converts the current record into a WiEDeviceDataObject.
        /// </summary>
        /// <param name="p_dataReader"></param>
        /// <returns></returns>
        private WiEDeviceDataObject GetDeviceDataObjectFromDataReader(SqlCeDataReader p_dataReader)
        {
            WiEDeviceDataObject deviceDataObject = null;

            if (p_dataReader.Read())
            {
                deviceDataObject = new WiEDeviceDataObject();

                deviceDataObject.DeviceID = (System.Guid)p_dataReader["guidDeviceID"];

                if (DBNull.Value != p_dataReader["guidMemberID_FK"])
                    deviceDataObject.MemberID = (System.Guid)p_dataReader["guidMemberID_FK"];

                if (DBNull.Value != p_dataReader["dtLastLocationDateCollected"])
                    deviceDataObject.LastLocationDateCollected = (DateTime)p_dataReader["dtLastLocationDateCollected"];

                if (DBNull.Value != p_dataReader["dtClientStarted"])
                    deviceDataObject.ClientStarted = (DateTime)p_dataReader["dtClientStarted"];

                if (DBNull.Value != p_dataReader["dtClientHeartbeat"])
                    deviceDataObject.ClientHeartbeat = (DateTime)p_dataReader["dtClientHeartbeat"];

                if (DBNull.Value != p_dataReader["flLastLocationLatitude"])
                    deviceDataObject.LastLocationLatitude = (double)p_dataReader["flLastLocationLatitude"];

                if (DBNull.Value != p_dataReader["flLastLocationLongitude"])
                    deviceDataObject.LastLocationLongitude = (double)p_dataReader["flLastLocationLongitude"];
            }

            return deviceDataObject;
        }

        #region IWiEModel Members

        /// <summary>
        /// Event raised when the location history table has been updated (i.e. new item added / removed, etc.)
        /// </summary>
        public event EventHandler<LocationHistoryChangedArgs> RaiseLocationHistoryChangedEvent;

        /// <summary>
        /// Event raised when the device table has been updated (i.e. new item added / removed, etc.)
        /// </summary>
        public event EventHandler<DeviceChangedArgs> RaiseDeviceChangedEvent;

        /// <summary>
        /// Event raised when the member table has been updated (i.e. new item added / removed, etc.)
        /// </summary>
        public event EventHandler<MemberChangedArgs> RaiseMemberChangedEvent;

        /// <summary>
        /// Saves Location Data Object to a local SQL Server compact database
        /// </summary>
        /// <param name="p_locationDataObject"></param>
        public void SaveLocation(WiELocationDataObject p_locationDataObject)
        {
            try
            {
                // Prepare our statement parameters (our local version of a stored procedure)

                // Required Parameters
                m_sqlceCommandInsertLocation.Parameters["@guidLocationID"].Value = p_locationDataObject.LocationID;
                m_sqlceCommandInsertLocation.Parameters["@flLatitude"].Value = p_locationDataObject.Latitude;
                m_sqlceCommandInsertLocation.Parameters["@flLongitude"].Value = p_locationDataObject.Longitude;
                m_sqlceCommandInsertLocation.Parameters["@nNumSatellites"].Value = p_locationDataObject.NumSatellites;

                // Optional Parameters (Nullable)
                if (p_locationDataObject.Speed != null)
                    m_sqlceCommandInsertLocation.Parameters["@flSpeed"].Value = p_locationDataObject.Speed;
                else
                    m_sqlceCommandInsertLocation.Parameters["@flSpeed"].Value = DBNull.Value;

                if (p_locationDataObject.Heading != null)
                    m_sqlceCommandInsertLocation.Parameters["@flHeading"].Value = p_locationDataObject.Heading;
                else
                    m_sqlceCommandInsertLocation.Parameters["@flHeading"].Value = DBNull.Value;

                if (p_locationDataObject.AltitudeWRTSeaLevel != null)
                    m_sqlceCommandInsertLocation.Parameters["@flAltitudeWRTSeaLevel"].Value = p_locationDataObject.AltitudeWRTSeaLevel;
                else
                    m_sqlceCommandInsertLocation.Parameters["@flAltitudeWRTSeaLevel"].Value = DBNull.Value;
                
                if (p_locationDataObject.AltitudeWRTEllipsoid != null)
                    m_sqlceCommandInsertLocation.Parameters["@flAltitudeWRTEllipsoid"].Value = p_locationDataObject.AltitudeWRTEllipsoid;
                else
                    m_sqlceCommandInsertLocation.Parameters["@flAltitudeWRTEllipsoid"].Value = DBNull.Value;

                m_sqlceCommandInsertLocation.Parameters["@dtTimeCollected"].Value = p_locationDataObject.DateCollected;
                
                if (p_locationDataObject.DeviceID != null)
                    m_sqlceCommandInsertLocation.Parameters["@guidDeviceID_FK"].Value = p_locationDataObject.DeviceID;
                else
                    m_sqlceCommandInsertLocation.Parameters["@guidDeviceID_FK"].Value = DBNull.Value;

                if (p_locationDataObject.MemberID != null)
                    m_sqlceCommandInsertLocation.Parameters["@guidMemberID_FK"].Value = p_locationDataObject.MemberID;
                else
                    m_sqlceCommandInsertLocation.Parameters["@guidMemberID_FK"].Value = DBNull.Value;

                // Attempt to save it
                m_sqlceCommandInsertLocation.ExecuteNonQuery();

                // Inform anyone that cares that we posted a new location
                if (RaiseLocationHistoryChangedEvent != null)
                    RaiseLocationHistoryChangedEvent(this, new LocationHistoryChangedArgs());
            }
            catch (Exception ex)
            {
                // There was an error saving the record
                System.Diagnostics.Trace.WriteLine("There was an issue saving the location: " + ex.ToString());

                throw new WiEModelException("SaveLocation() raised exception while attempting to save to local datastore.", ex);
            }
        }


        /// <summary>
        /// Initialize the model, set up any objects or threads that support the local data model.
        /// </summary>
        /// <remarks>
        /// 7/09/2008 - Added MemberID field to Location table and new Member table
        /// </remarks>
        public void Initialize()
        {
            try
            {
                // Open connection to our local datastor
                // ASSUMPTION: assume that .sdf file is in the same directory as the application assembly.
                m_sqlceConnection = new SqlCeConnection();
                m_sqlceConnection.ConnectionString = ("Data Source ="
                        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) 
                        + "\\" 
                        + SQLCE_DBNAME));

                m_sqlceConnection.Open();

                // Create prepared statement to store location history
                m_sqlceCommandInsertLocation = new SqlCeCommand();
                m_sqlceCommandInsertLocation.Connection = m_sqlceConnection;
                m_sqlceCommandInsertLocation.CommandType = CommandType.Text;
                m_sqlceCommandInsertLocation.CommandText = "INSERT INTO tabDeviceLocationHistory(guidLocationID,flLatitude,flLongitude,flSpeed, flHeading, flAltitudeWRTSeaLevel,flAltitudeWRTEllipsoid, nNumSatellites, dtTimeCollected, guidDeviceID_FK, guidMemberID_FK) VALUES(@guidLocationID,@flLatitude,@flLongitude,@flSpeed,@flHeading,@flAltitudeWRTSeaLevel,@flAltitudeWRTEllipsoid,@nNumSatellites,@dtTimeCollected,@guidDeviceID_FK,@guidMemberID_FK)";
                // OLD: m_sqlceCommandSaveLocation.CommandText = "INSERT INTO tabDeviceLocationHistory(guidLocationID,flLatitude,flLongitude,flSpeed, flHeading, flAltitudeWRTSeaLevel,flAltitudeWRTEllipsoid, nNumSatellites, dtTimeCollected, guidDeviceID_FK) VALUES(@guidLocationID,@flLatitude,@flLongitude,@flSpeed,@flHeading,@flAltitudeWRTSeaLevel,@flAltitudeWRTEllipsoid,@nNumSatellites,@dtTimeCollected,@guidDeviceID_FK)";
                SqlCeParameter sqlceParam = new SqlCeParameter("@guidLocationID",SqlDbType.UniqueIdentifier);
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flLatitude",SqlDbType.Float);
                sqlceParam.IsNullable = false;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flLongitude",SqlDbType.Float);
                sqlceParam.IsNullable = false;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flSpeed", SqlDbType.Float);
                sqlceParam.IsNullable = true;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flHeading", SqlDbType.Float);
                sqlceParam.IsNullable = true;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flAltitudeWRTSeaLevel", SqlDbType.Float);
                sqlceParam.IsNullable = true;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);        
                sqlceParam = new SqlCeParameter("@flAltitudeWRTEllipsoid", SqlDbType.Float);
                sqlceParam.IsNullable = true;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@nNumSatellites", SqlDbType.Int);
                sqlceParam.IsNullable = true;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtTimeCollected",SqlDbType.DateTime);
                sqlceParam.IsNullable = false;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@guidDeviceID_FK", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@guidMemberID_FK", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandInsertLocation.Parameters.Add(sqlceParam);
                m_sqlceCommandInsertLocation.Prepare();

                // Create the Delete Location Prepared Statement
                m_sqlceCommandDeleteLocation = new SqlCeCommand();
                m_sqlceCommandDeleteLocation.Connection = m_sqlceConnection;
                m_sqlceCommandDeleteLocation.CommandText = "DELETE FROM tabDeviceLocationHistory WHERE guidLocationID=@guidLocationID";
                sqlceParam = new SqlCeParameter("@guidLocationID",SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandDeleteLocation.Parameters.Add(sqlceParam);
                m_sqlceCommandDeleteLocation.Prepare();

                // Create the Retrieve Location Prepared Statement
                m_sqlceCommandRetrieveLocation = new SqlCeCommand();
                m_sqlceCommandRetrieveLocation.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveLocation.CommandText = "SELECT * FROM tabDeviceLocationHistory WHERE guidLocationID=@guidLocationID";
                sqlceParam = new SqlCeParameter("@guidLocationID", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveLocation.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveLocation.Prepare();

                // Create the Retrieve Locations from / to time prepared statement
                m_sqlceCommandRetrieveLocationsBetween = new SqlCeCommand();
                m_sqlceCommandRetrieveLocationsBetween.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveLocationsBetween.CommandText = "SELECT * FROM tabDeviceLocationHistory WHERE dtTimeCollected>=@dtStartTime AND dtTimeCollected<=@dtEndTime ORDER BY dtTimeCollected ASC";
                sqlceParam = new SqlCeParameter("@dtStartTime", SqlDbType.DateTime);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveLocationsBetween.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtEndTime", SqlDbType.DateTime);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveLocationsBetween.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveLocationsBetween.Prepare();

                // Create the retrieve locations that occured before a point in time prepared statement
                m_sqlceCommandRetrieveLocationBefore =new SqlCeCommand();
                m_sqlceCommandRetrieveLocationBefore.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveLocationBefore.CommandText = "SELECT * FROM tabDeviceLocationHistory WHERE dtTimeCollected<=@dtBeforeTime ORDER BY dtTimeCollected ASC"; 
                sqlceParam = new SqlCeParameter("@dtBeforeTime", SqlDbType.DateTime);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveLocationBefore.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveLocationBefore.Prepare();

                // Create the Retrieve Members statement
                m_sqlceCommandRetrieveMembers = new SqlCeCommand();
                m_sqlceCommandRetrieveMembers.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveMembers.CommandText = "SELECT * FROM tabMember";
                m_sqlceCommandRetrieveMembers.Prepare();

                // Create the Retrieve Member  statement
                m_sqlceCommandRetrieveMember = new SqlCeCommand();
                m_sqlceCommandRetrieveMember.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveMember.CommandText = "SELECT * FROM tabMember WHERE guidMemberID=@guidMemberID";
                sqlceParam = new SqlCeParameter("@guidMemberID", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveMember.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveMember.Prepare();

                // Create the Retrieve Member by Phone Number statement
                m_sqlceCommandRetrieveMemberByPhoneNumber = new SqlCeCommand();
                m_sqlceCommandRetrieveMemberByPhoneNumber.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveMemberByPhoneNumber.CommandText = "SELECT * FROM tabMember WHERE strPhoneNumber='@strPhoneNumber'";
                sqlceParam = new SqlCeParameter("@strPhoneNumber", SqlDbType.NVarChar,20);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveMemberByPhoneNumber.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveMemberByPhoneNumber.Prepare();

                // Create the Retrieve Member by UserName statement
                m_sqlceCommandRetrieveMemberByUserName = new SqlCeCommand();
                m_sqlceCommandRetrieveMemberByUserName.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveMemberByUserName.CommandText = "SELECT * FROM tabMember WHERE strUserName='@strUserName'";
                sqlceParam = new SqlCeParameter("@strUserName", SqlDbType.NVarChar,100);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveMemberByUserName.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveMemberByUserName.Prepare();

                // Create the Insert Member Command
                m_sqlceCommandInsertMember = new SqlCeCommand();
                m_sqlceCommandInsertMember.Connection = m_sqlceConnection;
                m_sqlceCommandInsertMember.CommandText = "INSERT INTO tabMember(guidMemberID,strFirstName,strLastName,strEmail,strPhoneNumber,strUserName,strUserPassword) VALUES(@guidMemberID,@strFirstName,@strLastName,@strEmail,@strPhoneNumber,@strUserName,@strUserPassword)";
                sqlceParam = new SqlCeParameter("@guidMemberID",SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strFirstName",SqlDbType.NVarChar,100);
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strLastName",SqlDbType.NVarChar,100);
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strEmail", SqlDbType.NVarChar,100); 
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strPhoneNumber",SqlDbType.NVarChar,20);
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strUserName",SqlDbType.NVarChar,100);
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strUserPassword", SqlDbType.NVarChar,100);
                m_sqlceCommandInsertMember.Parameters.Add(sqlceParam);
                m_sqlceCommandInsertMember.Prepare();

                // Create the Update Member Command
                m_sqlceCommandUpdateMember = new SqlCeCommand();
                m_sqlceCommandUpdateMember.Connection = m_sqlceConnection;
                m_sqlceCommandUpdateMember.CommandText = "UPDATE tabMember SET strFirstName = @strFirstName, strLastName = @strLastName ,strEmail = @strEmail, strPhoneNumber = @strPhoneNumber, strUserName = @strUserName, strUserPassword = @strUserPassword WHERE guidMemberID=@guidMemberID";
                sqlceParam = new SqlCeParameter("@guidMemberID", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strFirstName", SqlDbType.NVarChar,100);
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strLastName", SqlDbType.NVarChar,100);
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strEmail", SqlDbType.NVarChar,100);
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strPhoneNumber", SqlDbType.NVarChar,20);
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strUserName", SqlDbType.NVarChar,100);
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@strUserPassword", SqlDbType.NVarChar,100);
                m_sqlceCommandUpdateMember.Parameters.Add(sqlceParam);
                m_sqlceCommandUpdateMember.Prepare();

                // Device related prepared statements
                m_sqlceCommandRetrieveDevice = new SqlCeCommand();
                m_sqlceCommandRetrieveDevice.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveDevice.CommandText = "SELECT * FROM tabDevice WHERE guidDeviceID=@guidDeviceID";
                sqlceParam = new SqlCeParameter("@guidDeviceID", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveDevice.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveDevice.Prepare();

                m_sqlceCommandRetrieveDevicesByMember = new SqlCeCommand();
                m_sqlceCommandRetrieveDevicesByMember.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveDevicesByMember.CommandText = "SELECT * FROM tabDevice WHERE guidMemberID_FK=@guidMemberID";
                sqlceParam = new SqlCeParameter("@guidMemberID", SqlDbType.UniqueIdentifier);
                sqlceParam.IsNullable = false;
                m_sqlceCommandRetrieveDevicesByMember.Parameters.Add(sqlceParam);
                m_sqlceCommandRetrieveDevicesByMember.Prepare();

                m_sqlceCommandRetrieveDevices = new SqlCeCommand();
                m_sqlceCommandRetrieveDevices.Connection = m_sqlceConnection;
                m_sqlceCommandRetrieveDevices.CommandText = "SELECT * FROM tabDevice";
                m_sqlceCommandRetrieveDevices.Prepare();

                m_sqlceCommandInsertDevice = new SqlCeCommand();
                m_sqlceCommandInsertDevice.Connection = m_sqlceConnection;
                m_sqlceCommandInsertDevice.CommandText = "INSERT INTO tabDevice(guidDeviceID, guidMemberID_FK, flLastLocationLatitude,flLastLocationLongitude, dtLastLocationDateCollected, dtClientStarted, dtClientHeartbeat) VALUES(@guidDeviceID,@guidMemberID,@flLastLocationLatitude,@flLastLocationLongitude,@dtLastLocationDateCollected,@dtClientStarted,@dtClientHeartbeat)";
                sqlceParam = new SqlCeParameter("@guidDeviceID", SqlDbType.UniqueIdentifier);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@guidMemberID", SqlDbType.UniqueIdentifier);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flLastLocationLatitude", SqlDbType.Float);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flLastLocationLongitude", SqlDbType.Float);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtLastLocationDateCollected", SqlDbType.DateTime);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtClientStarted", SqlDbType.DateTime);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtClientHeartbeat", SqlDbType.DateTime);
                m_sqlceCommandInsertDevice.Parameters.Add(sqlceParam);
                m_sqlceCommandInsertDevice.Prepare();

                m_sqlceCommandUpdateDevice = new SqlCeCommand();
                m_sqlceCommandUpdateDevice.Connection = m_sqlceConnection;
                m_sqlceCommandUpdateDevice.CommandText = "UPDATE tabDevice SET guidMemberID_FK=@guidMemberID, flLastLocationLatitude=@flLastLocationLatitude, flLastLocationLongitude=@flLastLocationLongitude, dtLastLocationDateCollected=@dtLastLocationDateCollected, dtClientStarted=@dtClientStarted, dtClientHeartbeat=@dtClientHeartbeat WHERE guidDeviceID=@guidDeviceID";
                sqlceParam = new SqlCeParameter("@guidMemberID", SqlDbType.UniqueIdentifier);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flLastLocationLatitude", SqlDbType.Float);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@flLastLocationLongitude", SqlDbType.Float);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtLastLocationDateCollected", SqlDbType.DateTime);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtClientStarted", SqlDbType.DateTime);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@dtClientHeartbeat", SqlDbType.DateTime);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                sqlceParam = new SqlCeParameter("@guidDeviceID", SqlDbType.UniqueIdentifier);
                m_sqlceCommandUpdateDevice.Parameters.Add(sqlceParam);
                m_sqlceCommandUpdateDevice.Prepare();
            }
            catch (Exception ex)
            {
                // We had a problem connecting to the SQL Compact Database
                System.Diagnostics.Trace.WriteLine("Problem connecting to SQL Compact: " + ex.ToString());

                throw new WiEModelException("Initialize() raised exception while attempting to initialize local datastore.", ex);
            }
        }

        /// <summary>
        /// Handle any resource freeing (threads, objects) as the model is being "unloaded".
        /// </summary>
        public void Terminate()
        {
            if (m_sqlceCommandInsertLocation != null)
            {
                m_sqlceCommandInsertLocation = null;
            }

            if (m_sqlceConnection != null)
            {
                if (m_sqlceConnection.State != ConnectionState.Closed)
                {
                    m_sqlceConnection.Close();
                }
                m_sqlceConnection = null;
            }
        }

        /// <summary>
        /// Query the local datastore for the specified LocationID.
        /// </summary>
        /// <param name="p_guidLocationID">Unique identifier for the location</param>
        /// <returns></returns>
        public WiELocationDataObject GetLocation(Guid p_guidLocationID)
        {
            m_sqlceCommandRetrieveLocation.Parameters["@guidLocationID"].Value = p_guidLocationID;

            SqlCeDataReader dataReader = m_sqlceCommandRetrieveLocation.ExecuteReader();

            return (GetLocationDataObjectFromDataReader(dataReader));
        }

        /// <summary>
        /// Removes the location specified from the location table
        /// </summary>
        /// <param name="p_guidLocationID"></param>
        public void RemoveLocation(Guid p_guidLocationID)
        {
            m_sqlceCommandDeleteLocation.Parameters["@guidLocationID"].Value = p_guidLocationID;

            m_sqlceCommandDeleteLocation.ExecuteNonQuery();
        }

        /// <summary>
        /// Returns the list of location objects that match the search criteria (From..To time).
        /// </summary>
        /// <param name="p_dtStartTimeUTC"></param>
        /// <param name="p_dtStopTimeUTC"></param>
        /// <returns></returns>
        public List<WiELocationDataObject> GetLocationHistoryBetween(DateTime p_dtStartTimeUTC, DateTime p_dtStopTimeUTC)
        {
            List<WiELocationDataObject> listOfLocationDataObjects = new List<WiELocationDataObject>();

            // Query the local database for all the records between p_dtStartTimeUTC and p_dtStopTimeUTC
            m_sqlceCommandRetrieveLocationsBetween.Parameters["@dtStartTime"].Value=p_dtStartTimeUTC;
            m_sqlceCommandRetrieveLocationsBetween.Parameters["@dtEndTime"].Value = p_dtStopTimeUTC;
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveLocationsBetween.ExecuteReader();

            // Populate LocationDataObject list from the query results.
            WiELocationDataObject locationDataObject = null;
            while((locationDataObject = GetLocationDataObjectFromDataReader(dataReader))!=null)
                listOfLocationDataObjects.Add(locationDataObject);

            return(listOfLocationDataObjects);
        }

        /// <summary>
        /// Returns the list of location objects that match the search criteria (Before...)
        /// </summary>
        /// <param name="p_dtEndTimeUTC"></param>
        /// <returns></returns>
        public List<WiELocationDataObject> GetLocationHistoryBefore(DateTime p_dtEndTimeUTC)
        {
            List<WiELocationDataObject> listOfLocationDataObjects = new List<WiELocationDataObject>();

            m_sqlceCommandRetrieveLocationBefore.Parameters["@dtBeforeTime"].Value = p_dtEndTimeUTC;

            SqlCeDataReader dataReader = m_sqlceCommandRetrieveLocationBefore.ExecuteReader();

            WiELocationDataObject locationDataObject = null;
            while ((locationDataObject = GetLocationDataObjectFromDataReader(dataReader)) != null)
                listOfLocationDataObjects.Add(locationDataObject);

            return (listOfLocationDataObjects);
        }

        /// <summary>
        /// Saves a member to the local compact database
        /// </summary>
        /// <param name="p_memberDataObject"></param>
        public void SaveMember(WiEMemberDataObject p_memberDataObject)
        {
            SqlCeCommand saveCommand = null;

            try
            {
                // Are we doing an update or an insert?  select the appropriate command
                if (GetMember(p_memberDataObject.MemberID) != null)
                    saveCommand = m_sqlceCommandUpdateMember;
                else
                    saveCommand = m_sqlceCommandInsertMember;

                // Prepare our statement parameters (our local version of a stored procedure)
                saveCommand.Parameters["@guidMemberID"].Value = p_memberDataObject.MemberID;

                if (p_memberDataObject.FirstName != null)
                    saveCommand.Parameters["@strFirstName"].Value = p_memberDataObject.FirstName;
                else
                    saveCommand.Parameters["@strFirstName"].Value = DBNull.Value;

                if (p_memberDataObject.LastName != null)
                    saveCommand.Parameters["@strLastName"].Value = p_memberDataObject.LastName;
                else
                    saveCommand.Parameters["@strLastName"].Value = DBNull.Value;

                if (p_memberDataObject.Email != null)
                    saveCommand.Parameters["@strEmail"].Value = p_memberDataObject.Email;
                else
                    saveCommand.Parameters["@strEmail"].Value = DBNull.Value;

                if (p_memberDataObject.PhoneNumber != null)
                    saveCommand.Parameters["@strPhoneNumber"].Value = p_memberDataObject.PhoneNumber;
                else
                    saveCommand.Parameters["@strPhoneNumber"].Value = DBNull.Value;

                if (p_memberDataObject.UserName != null)
                    saveCommand.Parameters["@strUserName"].Value = p_memberDataObject.UserName;
                else
                    saveCommand.Parameters["@strUserName"].Value = DBNull.Value;

                if (p_memberDataObject.Password != null)
                    saveCommand.Parameters["@strUserPassword"].Value = p_memberDataObject.Password;
                else
                    saveCommand.Parameters["@strUserPassword"].Value = DBNull.Value;

                // Attempt to save it
                saveCommand.ExecuteNonQuery();

                // Let anyone know that cares that we just updated or added a member data object in the local datastore.
                if (RaiseMemberChangedEvent != null)
                    RaiseMemberChangedEvent(this, new MemberChangedArgs(p_memberDataObject));
            }
            catch (Exception ex)
            {
                // There was an error saving the record
                System.Diagnostics.Trace.WriteLine("There was an issue saving the member: " + ex.ToString());

                throw new WiEModelException("SaveMember() raised exception while attempting to save member to local datastore.", ex);
            }
        }

        /// <summary>
        /// Retrieves the Member associated with p_guidMemberID from the local database
        /// </summary>
        /// <param name="p_guidMemberID"></param>
        /// <returns></returns>
        public WiEMemberDataObject GetMember(Guid p_guidMemberID)
        {
            // Specify the parameter for the prepared statement
            m_sqlceCommandRetrieveMember.Parameters["@guidMemberID"].Value = p_guidMemberID;

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveMember.ExecuteReader();

            // Now convert the datareader record to a Member Data Object
            return (GetMemberDataObjectFromDataReader(dataReader));
        }

        /// <summary>
        /// Retrieves the member associated with the specified username p_strUserName
        /// </summary>
        /// <param name="p_strUserName"></param>
        /// <returns></returns>
        public WiEMemberDataObject GetMemberByUserName(string p_strUserName)
        {
            // Specify the parameter for the prepared statement
            m_sqlceCommandRetrieveMemberByUserName.Parameters["@strUserName"].Value = p_strUserName;

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveMemberByUserName.ExecuteReader();

            // Now convert the datareader record to a Member Data Object
            return (GetMemberDataObjectFromDataReader(dataReader));
        }

        /// <summary>
        /// Retrieve the member associated with the specified phone number p_strPhoneNumber
        /// </summary>
        /// <param name="p_strPhoneNumber"></param>
        /// <returns></returns>
        public WiEMemberDataObject GetMemberByPhoneNumber(string p_strPhoneNumber)
        {
            // Specify the parameter for the prepared statement
            m_sqlceCommandRetrieveMemberByPhoneNumber.Parameters["@strPhoneNumber"].Value = p_strPhoneNumber;

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveMemberByPhoneNumber.ExecuteReader();

            // Now convert the datareader record to a Member Data Object
            return (GetMemberDataObjectFromDataReader(dataReader));
        }

        /// <summary>
        /// Saves the device information to the local database
        /// </summary>
        /// <param name="p_deviceDataObject"></param>
        public void SaveDevice(WiEDeviceDataObject p_deviceDataObject)
        {
            SqlCeCommand saveCommand = null;

            try
            {
                // Are we doing an update or an insert?  select the appropriate command
                if (GetDevice(p_deviceDataObject.DeviceID) != null)
                    saveCommand = m_sqlceCommandUpdateDevice;
                else
                    saveCommand = m_sqlceCommandInsertDevice;

                // Prepare our statement parameters (our local version of a stored procedure)
                saveCommand.Parameters["@guidDeviceID"].Value = p_deviceDataObject.DeviceID;

                if (p_deviceDataObject.MemberID != null)
                    saveCommand.Parameters["@guidMemberID"].Value = p_deviceDataObject.MemberID;
                else
                    saveCommand.Parameters["@guidMemberID"].Value = DBNull.Value;

                if (p_deviceDataObject.ClientStarted != null)
                    saveCommand.Parameters["@dtClientStarted"].Value = p_deviceDataObject.ClientStarted;
                else
                    saveCommand.Parameters["@dtClientStarted"].Value = DBNull.Value;

                if (p_deviceDataObject.ClientHeartbeat != null)
                    saveCommand.Parameters["@dtClientHeartbeat"].Value = p_deviceDataObject.ClientHeartbeat;
                else
                    saveCommand.Parameters["@dtClientHeartbeat"].Value = DBNull.Value;

                if (p_deviceDataObject.LastLocationDateCollected != null)
                    saveCommand.Parameters["@dtLastLocationDateCollected"].Value = p_deviceDataObject.LastLocationDateCollected;
                else
                    saveCommand.Parameters["@dtLastLocationDateCollected"].Value = DBNull.Value;

                if (p_deviceDataObject.LastLocationLatitude != null)
                    saveCommand.Parameters["@flLastLocationLatitude"].Value = p_deviceDataObject.LastLocationLatitude;
                else
                    saveCommand.Parameters["@flLastLocationLatitude"].Value = DBNull.Value;

                if (p_deviceDataObject.LastLocationLongitude != null)
                    saveCommand.Parameters["@flLastLocationLongitude"].Value = p_deviceDataObject.LastLocationLongitude;
                else
                    saveCommand.Parameters["@flLastLocationLongitude"].Value = DBNull.Value;

                // Attempt to save it
                saveCommand.ExecuteNonQuery();

                // Let anyone know that cares that we just updated the local data store with a new or update device record.
                if (RaiseDeviceChangedEvent != null)
                    RaiseDeviceChangedEvent(this, new DeviceChangedArgs(p_deviceDataObject));
            }
            catch (Exception ex)
            {
                // There was an error saving the record
                System.Diagnostics.Trace.WriteLine("There was an issue saving the device: " + ex.ToString());

                throw new WiEModelException("SaveDevice() raised exception while attempting to save device to local datastore.", ex);
            }
        }

        /// <summary>
        /// Retrieve the device associated with the specified p_guidDeviceID.
        /// </summary>
        /// <param name="p_guidDeviceID"></param>
        /// <returns></returns>
        public WiEDeviceDataObject GetDevice(Guid p_guidDeviceID)
        {
            // Specify the parameter for the prepared statement
            m_sqlceCommandRetrieveDevice.Parameters["@guidDeviceID"].Value = p_guidDeviceID;

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveDevice.ExecuteReader();

            // Now convert the datareader record to a Member Data Object
            return (GetDeviceDataObjectFromDataReader(dataReader));
        }

        /// <summary>
        /// Returns the list of devices for the member (should be 1 on the local device).
        /// </summary>
        /// <param name="p_guidMemberID"></param>
        /// <returns></returns>
        public List<WiEDeviceDataObject> GetDevicesByMember(Guid p_guidMemberID)
        {
            List<WiEDeviceDataObject> listOfDevices = new List<WiEDeviceDataObject>();

            // Specify the parameter for the prepared statement
            m_sqlceCommandRetrieveDevicesByMember.Parameters["@guidMemberID"].Value = p_guidMemberID;

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveDevicesByMember.ExecuteReader();

            WiEDeviceDataObject deviceDataObject = null;
            while ((deviceDataObject = GetDeviceDataObjectFromDataReader(dataReader)) != null)
                listOfDevices.Add(deviceDataObject);

            // Now convert the datareader record to a Member Data Object
            return (listOfDevices);
        }

        /// <summary>
        /// Returns true if we have a connection to the SQL Server Compact Database
        /// </summary>
        /// <returns></returns>
        public bool IsAvailable()
        {
            return ((m_sqlceConnection.State & ConnectionState.Open)>0);
        }

        /// <summary>
        /// Retrieve all the members currently stored in the local store.
        /// </summary>
        /// <returns></returns>
        public List<WiEMemberDataObject> GetMembers()
        {
            List<WiEMemberDataObject> listOfMembers = new List<WiEMemberDataObject>();

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveMembers.ExecuteReader();
            WiEMemberDataObject memberDataObject = null;
            while ((memberDataObject = GetMemberDataObjectFromDataReader(dataReader)) != null)
                listOfMembers.Add(memberDataObject);

            return listOfMembers;
        }

        /// <summary>
        /// Retrieve all the devices currently stored in the local store.
        /// </summary>
        /// <returns></returns>
        public List<WiEDeviceDataObject> GetDevices()
        {
            List<WiEDeviceDataObject> listOfDevices = new List<WiEDeviceDataObject>();

            // Execute the query
            SqlCeDataReader dataReader = m_sqlceCommandRetrieveDevices.ExecuteReader();

            WiEDeviceDataObject deviceDataObject = null;
            while ((deviceDataObject = GetDeviceDataObjectFromDataReader(dataReader)) != null)
                listOfDevices.Add(deviceDataObject);

            // Now convert the datareader record to a Member Data Object
            return (listOfDevices);
        }

        #endregion
    }
}
